{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use only the stocks in S&P 500 Index here for demonstration."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "ticks_snp = pd.read_csv('../data/raw/S&P500_constituents.csv')['Symbol'].values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The raw data consists of .csv files, one for each stock, which contains market data such as $open, $close, $high, $low and $volume. We convert them into data frames containing all stocks of interest, one for each data type. Also, we take a limited time frame, Year 2018 to 2019 for the sake of training time in this demo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "in_path = '../data/raw/stocks'\n",
    "out_path = '../data/processed'\n",
    "\n",
    "for col in ['Open', 'Close', 'High', 'Low', 'Volume']:\n",
    "    dfs = []\n",
    "    for tick in ticks_snp: # Subset to stocks in S&P500\n",
    "        file_name = tick + '.csv'\n",
    "        file_path = os.path.join(in_path, file_name)\n",
    "        if os.path.exists(file_path):\n",
    "            df = pd.read_csv(file_path, index_col=0)\n",
    "            # Subset to Year 2018-2019\n",
    "            df_sub = df[[col]][(df.index > '2018') & (df.index < '2020')].round(2)\n",
    "            df_sub.columns = [tick]\n",
    "            dfs.append(df_sub)\n",
    "    df_comb = pd.concat(dfs, axis=1).sort_index()\n",
    "    df_comb = df_comb[sorted(df_comb.columns)].dropna(how='all')\n",
    "    df_comb.to_csv(os.path.join(out_path, col + '.csv'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use the $open data to compute the daily forward return, which will be used a the response that we want to predict."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_open = pd.read_csv(os.path.join(out_path, 'Open.csv'), index_col=0)\n",
    "df_open_forward1 = df_open.shift(periods=-1)\n",
    "df_open_forward2 = df_open.shift(periods=-2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "forward_return = (df_open_forward1 / df_open_forward2).round(4)\n",
    "forward_return.to_csv(os.path.join(out_path, 'ForwardReturn.csv'))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "assignment_venv",
   "language": "python",
   "name": "assignment_venv"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
